﻿USE [GestionePolveri]
GO
/****** Object:  StoredProcedure [GP].[QuickReportDetails]    Script Date: 03/29/2013 17:33:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Giuseppe Dattilo
-- Create date: 21/08/2012
-- Description:    Carica i dati relativi al dettaglio del cosidetto quick report...
-- =============================================
CREATE PROCEDURE [GP].[QuickReportDetails]
    @DatamovimentoFrom          DateTime     = NULL,
    @DatamovimentoTo            DateTime     = NULL,
    @Famiglia                   Varchar(100) = NULL,
    @Impianto                   Varchar(100) = NULL,
    @Applicazione               Char(1)      = NULL

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
   
    with cteFamiglia(IDPolvere, descrizione, IDFamiglia) AS
    (
        SELECT DISTINCT A.IDPolvere, F.descrizione, F.IDFamiglia
        FROM GP.AnagPolveri as A
        INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere
        INNER JOIN GP.AnagColori AS C ON CC.IDColore = C.IDColore
        INNER JOIN GP.AnagFamiglie AS F ON C.IDFamiglia = F.IDFamiglia
    ),
    cteApplicazioni(IDPolvere, Applicazione) AS
    (
        SELECT DISTINCT A.IDPolvere, CC.Applicazione
        FROM GP.AnagPolveri as A
        INNER JOIN GP.ComposizioneColori as CC ON A.IDPolvere = CC.IDPolvere
    )
    select
    Applicazione,
    Famiglia,
    sum(TotKg) as TotKG
    ,sum(NTot) as TotNP
    ,sum(Cons_RAL1) as Cons_RAL1
    ,sum(NP_RAL1) as NP_RAL1
    ,sum(Cons_RAL2) as Cons_RAL2
    ,sum(NP_RAL2) as NP_RAL2
    ,sum(Cons_Verticale) as Cons_Verticale
    ,sum(NP_Verticale) as NP_Verticale
    ,sum(Cons_Sublimato) as Cons_Sublimato
    ,sum(NP_Sublimato) as NP_Sublimato
    ,sum(Cons_ColorwoodGrande) as Cons_ColorwoodGrande
    ,sum(NP_ColorwoodGrande) as NP_ColorwoodGrande
    ,sum(Cons_Ferro) as Cons_Ferro
    ,sum(NP_Ferro) as NP_Ferro
    ,sum(Cons_AllineamentoInventario) as Cons_AllineamentoInventario
    ,sum(NP_AllineamentoInventatio) as NP_AllineamentoInventatio
    ,sum(Cons_Reso) as Cons_Reso
    ,sum(NP_Reso) as NP_Reso
    ,sum(Cons_Venduto) as Cons_Venduto
    ,sum(NP_Venduto) as NP_Venduto
    ,sum(Cons_Smaltito) as Cons_Smaltito
    ,sum(NP_Smaltito) as NP_Smaltito
    ,sum(Cons_ColorwoodPiccolo) as Cons_ColorwoodPiccolo
    ,sum(NP_ColorwoodPiccolo) as NP_ColorwoodPiccolo
    ,sum(Cons_Correzione) as Cons_Correzione
    ,sum(NP_Correzione) as NP_Correzione
   
    from (
   
    SELECT
   
    Stuff(( select ',' + Sub.Applicazione AS [text()] from cteApplicazioni as Sub where Sub.IDPolvere = cteApplicazioni.IDPolvere for xml path('')), 1, 1, '') as Applicazione 
    ,Stuff(( select ',' + Sub.Descrizione AS [text()] from cteFamiglia as Sub where Sub.IDPolvere = cteFamiglia.IDPolvere for xml path('')), 1, 1, '') as Famiglia  
   
    ,isnull(SC.[RAL1], 0.00) as Cons_RAL1
    ,isnull(ST.[RAL1], 0.00) as NP_RAL1   
    ,isnull(SC.[RAL2], 0.00) as Cons_RAL2
    ,isnull(ST.[RAL2], 0.00) as NP_RAL2   
    ,isnull(SC.[Verticale], 0.00) as Cons_Verticale
    ,isnull(ST.[Verticale], 0.00) as NP_Verticale
    ,isnull(SC.[Sublimato], 0.00) as Cons_Sublimato
    ,isnull(ST.[Sublimato], 0.00) as NP_Sublimato   
    ,isnull(SC.[Colorwood Grande], 0.00) as Cons_ColorwoodGrande
    ,isnull(ST.[Colorwood Grande], 0.00) as NP_ColorwoodGrande   
    ,isnull(SC.[Ferro], 0.00) as Cons_Ferro
    ,isnull(ST.[Ferro], 0.00) as NP_Ferro   
    ,isnull(SC.[Allineamento inventario], 0.00) as Cons_AllineamentoInventario
    ,isnull(ST.[Allineamento inventario], 0.00) as NP_AllineamentoInventatio   
    ,isnull(SC.[Reso], 0.00) as Cons_Reso
    ,isnull(ST.[Reso], 0.00) as NP_Reso   
    ,isnull(SC.[Venduto], 0.00) as Cons_Venduto
    ,isnull(ST.[Venduto], 0.00) as NP_Venduto   
    ,isnull(SC.[Smaltito], 0.00) as Cons_Smaltito
    ,isnull(ST.[Smaltito], 0.00) as NP_Smaltito   
    ,isnull(SC.[Colorwood Piccolo], 0.00) as Cons_ColorwoodPiccolo
    ,isnull(ST.[Colorwood Piccolo], 0.00) as NP_ColorwoodPiccolo   
    ,isnull(SC.[Inventario], 0.00) as Cons_Correzione
    ,isnull(ST.[Inventario], 0.00) as NP_Correzione
    ,isnull(TPU.TotScarico, 0.00) as TotKg
    ,isnull(TPU1.TotPrelievi, 0.00) as NTot
   
    FROM GP.Movimenti as P1 with (nolock)
    inner join GP.FamiglieColoriXPolveri on P1.IDPolvere = GP.FamiglieColoriXPolveri.IDPolvere
    left outer join cteFamiglia on cteFamiglia.IdPolvere = p1.IDPolvere
    left outer join cteApplicazioni on cteApplicazioni.IDPolvere = p1.IDPolvere
    left outer join (
        select * from
        (
            SELECT
            GP.Movimenti.idpolvere, GP.Movimenti.Quantita, GP.Impianti.Impianto
            FROM GP.Movimenti
            INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
            INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale   
            INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto      
            where GP.causalimagazzino.codcausale = 'S'
                  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
                  and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, ','))))
        ) DataTable
        PIVOT
        (
           sum(quantita)
           FOR impianto IN ([RAL1],[RAL2],[Verticale],[Sublimato],[Colorwood Grande],
                                    [Ferro],[Allineamento inventario],[Reso],[Venduto],[Smaltito],[Colorwood Piccolo], [Inventario])
        ) PivotTable
    ) as SC on P1.idpolvere = SC.idpolvere
    left outer join (
        select * from
        (
            SELECT
            GP.Movimenti.idpolvere, GP.Movimenti.Quantita, GP.Impianti.Impianto
            FROM GP.Movimenti
            INNER JOIN GP.Impianti ON GP.Movimenti.IDImpianto = GP.Impianti.IDImpianto
            INNER JOIN GP.causalimagazzino ON GP.Movimenti.IDcausale = GP.causalimagazzino.IDcausale
            INNER JOIN GP.Lotti ON GP.Lotti.idlotto = GP.movimenti.idlotto          
            where GP.causalimagazzino.codcausale = 'S'
                  and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (GP.Movimenti.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
                  and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, ','))))
        ) DataTable
        PIVOT
        (
           count(quantita)
           FOR impianto IN ([RAL1],
                            [RAL2],
                            [Verticale],
                            [Sublimato],
                            [Colorwood Grande],
                            [Ferro],
                            [Allineamento inventario],
                            [Reso],
                            [Venduto],
                            [Smaltito],
                            [Colorwood Piccolo],
                            [Inventario])
        ) PivotTable
    ) as ST on P1.idpolvere = ST.idpolvere
    outer apply (
        select SUM(quantita) as TotScarico
        from gp.Movimenti as mov
        INNER JOIN GP.Impianti ON mov.IDImpianto = GP.Impianti.IDImpianto
        INNER JOIN GP.causalimagazzino ON mov.IDcausale = GP.causalimagazzino.IDcausale
        where mov.idpolvere = p1.idpolvere
        and GP.causalimagazzino.codcausale = 'S'
        and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (mov.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
        and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, ','))))
    ) as TPU
    outer apply (
        select COUNT(quantita) as TotPrelievi
        from gp.Movimenti as mov
        INNER JOIN GP.Impianti ON mov.IDImpianto = GP.Impianti.IDImpianto
        INNER JOIN GP.causalimagazzino ON mov.IDcausale = GP.causalimagazzino.IDcausale
        where mov.idpolvere = p1.idpolvere
        and GP.causalimagazzino.codcausale = 'S'
        and (((@DatamovimentoFrom is null) and (@DatamovimentoTo is null)) OR (mov.DataMovimento between @DatamovimentoFrom and @DatamovimentoTo))
        and ((@Impianto is null) OR (GP.Impianti.IDImpianto IN (select param from GP.fn_Parametri(@Impianto, ','))))
    ) as TPU1   
    where
      --((@Famiglia is null) OR (cteFamiglia.IDFamiglia IN (select param from GP.fn_Parametri(@Famiglia, ','))))
      ((@Famiglia is null) OR (GP.FamiglieColoriXPolveri.Famiglie = @Famiglia))
      and ((@Applicazione is null) OR (cteApplicazioni.Applicazione Like @Applicazione))
       
    group by
    P1.IDPolvere
    ,SC.[RAL1],SC.[RAL2],SC.[Verticale],SC.[Sublimato],SC.[Colorwood Grande],SC.[Ferro],SC.[Allineamento inventario],SC.[Reso],SC.[Venduto],SC.[Smaltito],SC.[Colorwood Piccolo], SC.[Inventario]
    ,ST.[RAL1],ST.[RAL2],ST.[Verticale],ST.[Sublimato],ST.[Colorwood Grande],ST.[Ferro],ST.[Allineamento inventario],ST.[Reso],ST.[Venduto],ST.[Smaltito],ST.[Colorwood Piccolo], ST.[Inventario]
    ,cteFamiglia.IDPolvere
    ,cteApplicazioni.IDPolvere
    ,TPU.TotScarico
    ,TPU1.TotPrelievi
) as T
group by Applicazione, Famiglia

END